-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@cursor CURSOR
			, @file_path VARCHAR(512)
			, @object_name NVARCHAR(256)
			, @sql VARCHAR(8000)
-- ============

-- set root directory - sub-directories of \FUNCTION and \PROCEDURE should exist
DECLARE	@root_directory VARCHAR(512) = 'path\dependency'

-- ============
/*<code_comment><![CDATA[begin cursor: for each header object]]></code_comment>*/
SET @cursor = CURSOR FOR
	SELECT @root_directory + '\' + object_type + '\' + dpnd_object_name
		, dpnd_object_name
	FROM (
		SELECT dpnd_object_name
			, CASE WHEN object_type LIKE '%PROCEDURE' THEN 'PROCEDURE'
				WHEN object_type LIKE '%FUNCTION' THEN 'FUNCTION'
				ELSE UPPER(object_type)
				END AS object_type
		FROM ssf.dpnd_header_object
		WHERE object_type LIKE '%PROCEDURE'
		OR object_type LIKE '%FUNCTION'
	) o
	ORDER BY object_type

OPEN @cursor

FETCH NEXT FROM @cursor INTO @file_path
	, @object_name

WHILE @@FETCH_STATUS <> -1
BEGIN
	-- ============
	/*<code_comment><![CDATA[export object header as xml file]]></code_comment>*/
	SET @sql = 'bcp'
		+ ' "SELECT N''<?xml version=""1.0""?>'' + CAST(object_header AS NVARCHAR(MAX))'
			+ ' FROM ' + QUOTENAME(DB_NAME()) + '.ssf.dpnd_get_object_header(''' + @object_name + ''')"'
		+ ' queryout "' + @file_path + '.xml"'
		+ ' -w -t "" -T -S ' + @@SERVERNAME
	
	PRINT(@sql)
	
	EXECUTE master..xp_cmdshell @sql, no_output
	-- ============
	
	-- ============
	/*<code_comment><![CDATA[if exists, export object definition as text file]]></code_comment>*/
	IF OBJECT_DEFINITION(OBJECT_ID(@object_name)) IS NOT NULL
	BEGIN
		SET @sql = 'bcp'
			+ ' "SELECT [definition] FROM ' + QUOTENAME(DB_NAME()) + '.sys.sql_modules'
				+ ' WHERE [object_id] = OBJECT_ID(''' + DB_NAME() + '.' + @object_name + ''')"'
			+ ' queryout "' + @file_path + '.txt"'
			+ ' -w -t "" -T -S ' + @@SERVERNAME
		
		PRINT(@sql)
		
		EXECUTE master..xp_cmdshell @sql, no_output
	END
	-- ============

	FETCH NEXT FROM @cursor INTO @file_path
		, @object_name
END
/*<code_comment><![CDATA[end cursor: for each header object]]></code_comment>*/
-- ============
